package com.app.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import jxl.Cell;
import jxl.CellReferenceHelper;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
@SuppressWarnings({"unchecked","unused"})
public class ExcelUtil {
	/**
	 * 根据模板生成Excel文件
	 * @param template 模板文件
	 * @param outFile 输出文件
	 * @param data 每个List中第1位Intger 写入哪个sheet(从0开始) 
	 * 						  第2位String 从哪个单元格开始如A3  
	 * 						  第3位String Map中key的字符串以逗号分隔  
	 * 						  此后为Map实际数据 一个Map代表一行数据
	 * @throws Exception
	 */
	public static void writeWithTemplate(File template,File outFile,List... data) throws Exception{
		outFile.getParentFile().mkdirs();
		WritableWorkbook write = Workbook.createWorkbook(outFile, Workbook.getWorkbook(template));
		for(List list : data){
			if(list!=null && list.size()>3 && list.size()<65535){
				WritableSheet  sheet = write.getSheet((Integer)list.get(0));
				int c = CellReferenceHelper.getColumn((String)list.get(1));
				int r = CellReferenceHelper.getRow((String)list.get(1));
				String[] keys = ((String)list.get(2)).split(",");
				
				for(int i=3;i<list.size();i++){
					Map map = (Map)list.get(i);
					for(int j=0;j<keys.length;j++){
						Object obj = map.get(keys[j]);
						if(obj instanceof java.lang.Number){
							sheet.addCell(new jxl.write.Number(c+j,r,Double.parseDouble(obj+"")));
						}else{
							sheet.addCell(new Label(c+j,r,obj==null?"":obj+""));
						}
					}
					r++;
				}
			}
		}
		write.write();
		write.close();
	}
	
	public void excel_jxl() throws Exception {
		Workbook wb = Workbook.getWorkbook(new File("e:/aa1.xls"));
		int sheets = wb.getNumberOfSheets();
		for (int j = 0; j < sheets; j++) {
			Sheet sheet = wb.getSheet(j);
			Range[] ranges = sheet.getMergedCells();
			List<Range> rangeList = new ArrayList();
			for (int i = 0; i < ranges.length; i++) {
				if (ranges[i].getTopLeft().getColumn() == 4)
					rangeList.add(ranges[i]);
			}
			String sheetName = sheet.getName();
			if (!sheetName.contains("RA")) {
				String type = "";
				if (sheetName.contains("NE"))
					type = "2";
				else if (sheetName.contains("UNIT"))
					type = "3";
				else if (sheetName.contains("IF"))
					type = "4";
				else if (sheetName.contains("LINK"))
					type = "5";
				for (int i = 2; i < sheet.getRows(); i++) {
					String estr = "";
					String astr = sheet.getCell("A" + i).getContents();
					if(rangeList ==null || rangeList.isEmpty()){
						estr = sheet.getCell("E"+i).getContents();
					}else{
						for (Range range : rangeList) {
							Cell topCell = range.getTopLeft();
							Cell bottomCell = range.getBottomRight();
							if ((i - 1) >= topCell.getRow()	&& (i - 1) <= bottomCell.getRow()) {
								estr = topCell.getContents();
								estr = estr.substring(estr.indexOf(".") + 2, estr
										.indexOf("`$$"));
								break;
							}else{
								estr = sheet.getCell("E"+i).getContents();
							}
						}
					}
				}
			}
		}
	}
	
	
	public static int addCustomerReportSheet(WritableWorkbook wwb,String sheetName,List<Map> data) throws Exception{
		WritableSheet ws = wwb.createSheet(sheetName,0);//第一个sheet
		int lineNum = 0;//标题
        WritableFont wfc = new WritableFont(WritableFont.ARIAL,16,
        		WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
        WritableCellFormat wcfFC = new WritableCellFormat(wfc);    
        wcfFC.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); //Border是jxl.format.Border
        wcfFC.setBackground(Colour.GRAY_25);//设置单元格背景颜色为暗灰色
        wcfFC.setAlignment(jxl.format.Alignment.LEFT);//设置文本对其方式，左对齐还是右对齐
        ws.setRowView(lineNum,100);
        ws.mergeCells(0, lineNum, 3, lineNum);//合并第一行的0-3列
        ws.addCell(new Label(0,lineNum,sheetName,wcfFC));
        lineNum = lineNum +3;
        List<String> titleList = new ArrayList<String>();
        List<String> columnList = new ArrayList<String>();
        if(data!=null && data.size()>0){
        	Map m = data.get(0);
        	Set set = m.keySet();
        	Iterator it = set.iterator();
        	while(it.hasNext()){
        		String key = (String)it.next();
        		if(key==null) key = "";
        		titleList.add(key);
				columnList.add(key);
        	}
            ExcelUtil.addGrid("",titleList,columnList,data,ws,lineNum);
             return 1;
        }else{
        	return 0;
        }
   }
	
	
	
	/**
	 * Gets the text from a MS Excel document.
	 * 
	 * @param in
	 *            The InputStream representing the MS Excel file.
	 */
	@SuppressWarnings("deprecation")
	public static String extractText(InputStream in,List list) {
		StringBuffer sb = new StringBuffer();
		int numSheets = 0;
		HSSFWorkbook wb = null;
		try {
			wb = new HSSFWorkbook(in);
			numSheets = wb.getNumberOfSheets();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}

		for (int i = 0; i < numSheets; ++i) {
			try {
				HSSFSheet sheet = wb.getSheetAt(i);
				int numRows = sheet.getLastRowNum();
				for (int j = 0; j <= numRows; ++j) {
					try {
						HSSFRow row = sheet.getRow(j);
						int numCells = row.getLastCellNum();
						for (int k = 0; k < numCells; ++k) {
							try {
								HSSFCell cell = row.getCell((short) k);
								if (cell != null) {
									int type = cell.getCellType();
									if (type == HSSFCell.CELL_TYPE_STRING) {
										String str = cell.getStringCellValue();
										str = str.trim();
										//if(str == null)str = "aaa";
										list.add(str);
										//System.out.println(":::::"+str+":::::");
										str = replace(str, "\n", "");
										sb.append(str).append(" ");
									}
								}

							} catch (Exception e) {
								System.out
										.println("the CELL value is null,IGNORE it!");
							}
						} // cells
						sb.append("\n"); // break on each row
					} catch (Exception e) {
						System.out.println("the ROW value is null,IGNORE it!");
					}
					sb.append("\n");
				} // rows
				sb.append("\n"); // break on each sheet
			} catch (Exception e) {
				System.out.println("the SHEET value is null,IGNORE it!");
			}
		} // sheets
		return sb.toString();
	} // end extractText(InputStream in) method

	// This should really be made 'static' and moved into a utility class,
	// included here to simplify things
	private static final String replace(String line, String oldString,
			String newString) {
		if (line == null) {
			return null;
		}
		int i = 0;
		if ((i = line.indexOf(oldString, i)) >= 0) {
			char[] line2 = line.toCharArray();
			char[] newString2 = newString.toCharArray();
			int oLength = oldString.length();
			StringBuffer buf = new StringBuffer(line2.length);
			buf.append(line2, 0, i).append(newString2);
			i += oLength;
			int j = i;
			while ((i = line.indexOf(oldString, i)) > 0) {
				buf.append(line2, j, i - j).append(newString2);
				i += oLength;
				j = i;
			}
			buf.append(line2, j, line2.length - j);
			return buf.toString();
		}
		return line;
	}
	
	
	public static Map<String,List<Map>> getExcel(InputStream is){
		Map result = new HashMap();
		try {
			Workbook wb = Workbook.getWorkbook(is);
			int sheets = wb.getNumberOfSheets();
			for (int j = 0; j < sheets; j++) {
				List list = new ArrayList();
				Sheet sheet = wb.getSheet(j);
				String sheetName = sheet.getName();
				for(int r=1;r<sheet.getRows();r++){
					Map row = new HashMap();
					for(int c=0;c<sheet.getColumns();c++){
						String cell = sheet.getCell(c, r).getContents();
						row.put(sheet.getCell(c, 0).getContents(), cell);
					}
					list.add(row);
				}
				result.put(sheetName, list);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return result;
	}

	/**
	 * 读excel文件 返回数据
	 * 
	 * @param String
	 *            filepath 文件路径
	 * @return String data
	 * @throws IOException
	 * @throws Exception
	 */
	public static String readExcel(String filepath) throws Exception {
		FileInputStream in = new FileInputStream(filepath);
		List list = new ArrayList();
		
		String str = extractText(in,list);
		/*for(int i=0;i<list.size();i++){
			System.out.println(list.get(i).toString());
		}*/
		return str;
	}
	
	public static void main(String[] args)throws Exception{
		/*String str = MyFileUtil.readFileText(new FileInputStream(new File("F:/javaProject/DOQuery/1.txt")));
		String[] item = str.split("\n");
				
		System.out.println(item.length);
		for(String s:item){
			String[] s1 = s.split("\t");
			for(String s2:s1){
				if(s2.trim().length()>0){
					System.out.print(s2.trim()+"---");
				}
			}
			System.out.println();System.out.println();
		}*/
		System.out.println(readExcel("d:/3.xls").split("MSISDN")[1].trim());
	}
	
	public static String readExcel(FileInputStream in) throws Exception {
		List list = new ArrayList();
		String str = extractText(in,list);
		return str;
	}
	
	
	/**
	 * 读excel文件 返回集合数据
	 * 
	 * @param String
	 *            filepath 文件路径
	 * @return List data
	 * @throws IOException
	 * @throws Exception
	 */
	public static List readExcelList(String filepath) throws Exception {
		FileInputStream in = new FileInputStream(filepath);
		List list = new ArrayList();
		String str = extractText(in,list);		
		//List<List> list2 = getSubList(list,6);
		//System.out.println(list.size());
		return list;
	}
	
	//将List截取成数个List集合，按照一定数量截取
	public static List getSubList(List list,int num)throws Exception{		
		List<List> list3 = new ArrayList<List>();
		int num2 = 0;
		for(int i=0;i<=list.size()/num;i++){
			List list2 = null;
			num2 = num*i+num;
			if(num2<=list.size()){
				list2 = list.subList(num*i,num2);
				list3.add(list2);
			}
		}
		//List list2 = list.subList(num2,list.size()-num2);
		//list3.add(list2);
		return list3;
	}

	
	/*************************
	 * 在excel中添加一个grid
	 * @param title
	 * @param titleList
	 * @param columnList
	 * @param dataList
	 * @param ws
	 * @param lineNum
	 * @return
	 */
	public static int addGrid(String title,List<String> titleList,
			List<String> columnList,
			List<Map> dataList,
			WritableSheet ws,int lineNum){
		//列表标题
		try {
			if(!title.equals("") && title!=null){
				WritableFont wfc = new WritableFont(WritableFont.ARIAL,16,WritableFont.NO_BOLD,
						false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
				WritableCellFormat wcfFC = new WritableCellFormat(wfc);
				wcfFC.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); //Border是jxl.format.Border
				wcfFC.setBackground(Colour.GRAY_25);//设置单元格背景颜色为暗灰色
				wcfFC.setAlignment(jxl.format.Alignment.LEFT);//设置文本对其方式，左对齐还是右对齐
				//把 单元格（column, row）到单元格（column1, row1）进行合并。
				//sheetWrite.mergeCells(column, row, column1, row1);
				ws.mergeCells(0, lineNum, 3, lineNum);//合并第lineNum+1行的2-5列
				ws.addCell(new Label(0,lineNum,title,wcfFC));	
				ws.setColumnView(0, 10);
				ws.setColumnView(1, 10);
				lineNum = lineNum+1;
			}
			
			//列名   
			WritableFont wfc2 = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,
					UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
			WritableCellFormat wcfFC2 = new WritableCellFormat(wfc2);
			wcfFC2.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); //Border是jxl.format.Border
			wcfFC2.setBackground(Colour.GRAY_25);//设置单元格背景颜色为天蓝色
			wcfFC2.setAlignment(jxl.format.Alignment.LEFT);//设置文本对其方式，左对齐还是右对齐     
			ws.setRowView(0,350);
			
			WritableFont wfc3 = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,
					UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
			WritableCellFormat wcfFC3 = new WritableCellFormat(wfc3);
			//wcfFC3.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); //Border是jxl.format.Border
			wcfFC3.setAlignment(jxl.format.Alignment.LEFT);//设置文本对其方式，左对齐还是右对齐  
			
			//标题
			for(int i=0;i<titleList.size();i++){
				ws.addCell(new Label(i,lineNum,titleList.get(i),wcfFC2));
				ws.setColumnView(i, 25);
				/*if(i==0){
					ws.setColumnView(i, 25);
				}else if(i==1){
					ws.setColumnView(i, 18);
				}
				else{
					ws.setColumnView(i, 10);
				}*/
			}
			lineNum = lineNum+1;
			
			//循环数据
			for(int i=0;i<dataList.size();i++){
				Map m = (Map)dataList.get(i);
				for(int j=0;j<columnList.size();j++){
					String ti = "";
					try {
						ti = (String)columnList.get(j);
					} catch (Exception e) {
						try {
							ti = columnList.get(j).toString();
						} catch (Exception e1) {
						}
					}
					String ss = "";
					try {
						ss = (String)m.get(ti);
					} catch (Exception e) {
						try {
							ss = m.get(ti).toString();
						} catch (Exception e1) {
						}
					}
					ws.addCell(new Label(j,lineNum,ss));//0列1行	
				}
				lineNum = lineNum+1;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return lineNum;
	}
}
